package com.atguigu5;

import com.beans.Customers;
import com.utils.JDBCUtilsJiuBan;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * commons-dbutils 是apache组织提供的一个开源的JDBC工具类库，封装了针对于数据库的增删改查操作
 * @auther user
 * @date 2021/12/10 - 4:43
 */
public class QueryRunnerTest {
    @Test
    public void testInsert() {
        Connection connection = null;
        try {
            QueryRunner queryRunner = new QueryRunner();
            connection = JDBCUtilsJiuBan.getConnection();
            String sql = "insert into customers(name,email,birth) values(?,?,?)";
            int i = queryRunner.update(connection, sql, "蔡徐坤", "caixukun@qq.com", "2025-09-05");
            System.out.println("影响的条目数 i = " + i);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtilsJiuBan.closeResource(connection,null);
        }
    }
    // BeanHander:是ResultSetHandler接口的实现类，用于封装表中的一条记录
    @Test
    public void testQuery1() {
        Connection connection = null;
        try {
            QueryRunner queryRunner = new QueryRunner();
            connection = JDBCUtilsJiuBan.getConnection();
            String sql = "select id,name,email,birth from customers where id = ?";
            BeanHandler<Customers> handler = new BeanHandler<>(Customers.class);
            Customers customers = queryRunner.query(connection, sql, handler, 26);
            System.out.println("customers = " + customers);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtilsJiuBan.closeResource(connection,null);
        }
    }
    //BeanListHandler 用于封装表中多条记录构成的集合
    @Test
    public void testQuery2() {
        Connection connection = null;
        try {
            QueryRunner queryRunner = new QueryRunner();
            connection = JDBCUtilsJiuBan.getConnection();
            String sql = "select id,name,email,birth from customers where id < ?";
            BeanListHandler<Customers> handler = new BeanListHandler<>(Customers.class);
            List<Customers> customersList = queryRunner.query(connection, sql, handler, 26);
            customersList.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtilsJiuBan.closeResource(connection,null);
        }
    }
    //MapHandler 对应表中一条记录，将字段及相应的值作为map的key和value
    @Test
    public void testQuery3() {
        Connection connection = null;
        try {
            QueryRunner queryRunner = new QueryRunner();
            connection = JDBCUtilsJiuBan.getConnection();
            String sql = "select id,name,email,birth from customers where id = ?";
            MapHandler handler = new MapHandler();
            Map<String, Object> map = queryRunner.query(connection, sql, handler, 26);
            System.out.println("map = " + map);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtilsJiuBan.closeResource(connection,null);
        }
    }
    //MapListHandler  对应表中多条记录，将字段及相应的值作为map的key和value，将map添加到list中
    @Test
    public void testQuery4() {
        Connection connection = null;
        try {
            QueryRunner queryRunner = new QueryRunner();
            connection = JDBCUtilsJiuBan.getConnection();
            String sql = "select id,name,email,birth from customers where id < ?";
            MapListHandler handler = new MapListHandler();
            List<Map<String, Object>> mapList = queryRunner.query(connection, sql, handler, 26);
            mapList.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtilsJiuBan.closeResource(connection,null);
        }
    }
    //ScalarHandler  用于查询特殊值
    @Test
    public void testQuery5() {
        Connection connection = null;
        try {
            QueryRunner queryRunner = new QueryRunner();
            connection = JDBCUtilsJiuBan.getConnection();
            String sql = "select count(*) from customers";
            ScalarHandler handler = new ScalarHandler();
            Long count = (Long) queryRunner.query(connection, sql, handler);
            System.out.println("count(*) = " + count);

            sql = "select max(birth) from customers";
            handler = new ScalarHandler();
            Date maxBirth = (Date) queryRunner.query(connection, sql, handler);
            System.out.println("maxBirth = " + maxBirth);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtilsJiuBan.closeResource(connection,null);
        }
    }
    //自定义ResultSetHandler的实现类
    @Test
    public void testQuery6() {
        Connection connection = null;
        try {
            QueryRunner queryRunner = new QueryRunner();
            connection = JDBCUtilsJiuBan.getConnection();
            String sql = "select id,name,email,birth from customers where id = ?";
            //自定义ResultSetHandler的实现类
            ResultSetHandler<Customers> handler = new ResultSetHandler<Customers>() {
                @Override
                public Customers handle(ResultSet resultSet) throws SQLException {
                    //return new Customers(12,"成龙","jacky@qq.com",new Date(234234234L));
                    if (resultSet.next()){
                        int id = resultSet.getInt("id");
                        String name = resultSet.getString("name");
                        String email = resultSet.getString("email");
                        Date birth = resultSet.getDate("birth");
                        return new Customers(id,name,email,birth);
                    }
                    return null;
                }
            };
            Customers customers = queryRunner.query(connection, sql, handler, 26);
            System.out.println("customers = " + customers);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtilsJiuBan.closeResource(connection,null);
        }
    }
}
